package com.yanqu.road.dataHelper.action.excel;

import com.yanqu.road.dataHelper.gameServer.GameServer;
import com.yanqu.road.dataHelper.manager.ServerMgr;
import com.yanqu.road.dataHelper.repair.BaseRepair;
import com.yanqu.road.entity.servercenter.ServerInfo;
import com.yanqu.road.utils.date.DateHelper;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

public class ExpireDataLimitDelete extends BaseRepair {
    public ExpireDataLimitDelete(GameServer gameServer) {
        super(gameServer);
    }


    @Override
    public void repair(long param1, String param2) {

    }

    public void go() {
        ServerInfo serverInfo = getGameServer().getServerInfo();
        int limit = 1000;
        List<Long> allUserId = getAllUserId();
        Collections.sort(allUserId);
        List<Integer> taskActivityIdList = new ServerMgr().getTaskActivityIdList("2023-01-01 00:00:00");
        List<Integer> deleteActivityIdList = new ArrayList<>();
        long deleteCount = 0;
        for (Long userId : allUserId) {
            int hours = DateHelper.getHours();
            while (hours > 1 && hours < 4){
                try {
                    Thread.sleep(DateHelper.MINUTE_MILLIONS * 10);
                    hours = DateHelper.getHours();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
            long rowNum = 0;
//            deleteBeautySkillRefineLog(userId);
//            getLogger().error("user: {} delete beauty skill refine finish", userId);
//            deleteBeautyGlamourLog(userId);
//            getLogger().error("user: {} delete t_l_beauty_glamour finish", userId);
//            deletePatronsUpgradeLog(userId);
//            getLogger().error("user: {} delete t_l_patrons_upgrade finish", userId);
//            deleteClinicLog(userId);
//            getLogger().error("user: {} delete t_l_clinic_cure finish", userId);
//            deleteManorHarvestLog(userId);
//            getLogger().error("user: {} delete manor_harvest finish", userId);

            rowNum = deleteTradeWarRecord(userId);
            getLogger().error("user: {},count:{} deleteTradeWarRecord finish", userId, rowNum);
            deleteCount += rowNum;

            rowNum = deleteInteractiveRecord(userId);
            getLogger().error("user: {},count:{} deleteInteractiveRecord finish", userId, rowNum);
            deleteCount += rowNum;

            rowNum = deleteCricketBattleDetailLog(userId, 110);
            getLogger().error("user: {},count:{} deleteCricketBattleDetailLog finish", userId, rowNum);
            deleteCount += rowNum;

            rowNum = deleteCricketBattleLog(userId, 110);
            getLogger().error("user: {},count:{} deleteCricketBattleLog finish", userId, rowNum);
            deleteCount += rowNum;

            rowNum = deleteTaskActivityData(userId, taskActivityIdList, deleteActivityIdList);
            getLogger().error("user: {} deleteTaskActivityData finish", userId);
            deleteCount += rowNum;
//            deleteVehicleOtherSkill(userId);
//            deleteVehicleSkill(userId);
//            deleteVehicle(userId);

            getLogger().error("total delete count:{}", deleteCount);
        }

    }

    private List<Long> getAllUserId() {
        String sql = "select userId from t_u_user";
        List<Long> list = new ArrayList<>();
        ResultSet rs = null;
        CallableStatement call = null;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            rs = call.executeQuery();
            while (rs.next()) {
                list.add(rs.getLong("userId"));
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return list;
    }

    private List<Long> getUserListByLastLoginTime() {
        String sql = "select userId from t_u_user where lastLoginTime < ?";
        List<Long> list = new ArrayList<>();
        ResultSet rs = null;
        CallableStatement call = null;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(1, System.currentTimeMillis() / 1000 - 7 * DateHelper.DAY_SECONDS);
            rs = call.executeQuery();
            while (rs.next()) {
                list.add(rs.getLong("userId"));
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return list;
    }

    private boolean hasDeleteBeautySkillRefineLog(long userId) {
        String sql = "select * from t_l_beauty_skill_refine where userId = ? and time < ? limit 1";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - 1000L * 100 * 3600 * 24;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            rs = call.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return false;
    }

    private void deleteBeautySkillRefineLog(long userId) {
        String sql = "delete from t_l_beauty_skill_refine where userId = ? and time < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - DateHelper.DAY_MILLIONS * 180;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            int rowNum = call.executeUpdate();
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private boolean hasDeleteManorHarvestLog(long userId) {
        String sql = "select * from t_l_manor_harvest where userId = ? and time < ? limit 1";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - 1000L * 60 * 3600 * 24;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            rs = call.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return false;
    }


    private void deleteManorHarvestLog(long userId) {
        String sql = "delete from t_l_manor_harvest where userId = ? and time < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - DateHelper.DAY_MILLIONS * 90;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            int rowNum = call.executeUpdate();
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private boolean hasDeleteClinicLog(long userId) {
        String sql = "select * from t_l_clinic_cure where userId = ? and time < ? limit 1";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - 1000L * 100 * 3600 * 24;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            rs = call.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return false;
    }

    private void deleteClinicLog(long userId) {
        String sql = "delete from t_l_clinic_cure where userId = ? and time < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - DateHelper.DAY_MILLIONS * 180;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            int rowNum = call.executeUpdate();
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private boolean hasDeleteBeautyGlamourLog(long userId) {
        String sql = "select * from t_l_beauty_glamour where userId = ? and time < ? limit 1";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - 1000L * 100 * 3600 * 24;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            rs = call.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return false;
    }

    private void deleteBeautyGlamourLog(long userId) {
        String sql = "delete from t_l_beauty_glamour where userId = ? and time < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - DateHelper.DAY_MILLIONS * 180;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            int rowNum = call.executeUpdate();
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private boolean hasDeletePatronsUpgradeLog(long userId) {
        String sql = "select * from t_l_patrons_upgrade where userId = ? and time < ? limit 1";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - 1000L * 100 * 3600 * 24;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            rs = call.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return false;
    }

    private void deletePatronsUpgradeLog(long userId) {
        String sql = "delete from t_l_patrons_upgrade where userId = ? and time < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() - DateHelper.DAY_MILLIONS * 180;
        try {
            call = getGameServer().getLogConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setTimestamp(i++, new Timestamp(lastTime));
            int rowNum = call.executeUpdate();
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private boolean hasTradeWarRecord(long userId) {
        String sql = "select * from t_u_trade_war_record where userId = ? and recordTime < ? limit 1";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis()/1000 - 60 * 3600 * 24;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, lastTime);
            rs = call.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return false;
    }

    private int deleteTradeWarRecord(long userId) {
        String sql = "delete from t_u_trade_war_record where userId = ? and recordTime < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis()/1000 - DateHelper.DAY_SECONDS * 180;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, lastTime);
            int rowNum = call.executeUpdate();
            return rowNum;
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return 0;
    }

    private int deleteInteractiveRecord(long userId) {
        String sql = "delete from t_u_interactive_record where userId = ? and recordTime < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis()/1000 - DateHelper.DAY_SECONDS * 180;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, lastTime);
            int rowNum = call.executeUpdate();
            return rowNum;
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return 0;
    }

    private int deleteCricketBattleDetailLog(long userId, int limit) {
        String sql = "DELETE FROM t_u_cricket_battle_log_detail WHERE battleLogId IN(" +
                "SELECT t1.id FROM (SELECT id FROM t_u_cricket_battle_log WHERE userId = ?) t1 WHERE t1.id" +
                " NOT IN (SELECT t2.id FROM (SELECT id FROM t_u_cricket_battle_log WHERE userId = ? ORDER BY createTime DESC LIMIT ?) t2))";
        ResultSet rs = null;
        CallableStatement call = null;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, userId);
            call.setInt(i++, limit);
            int rowNum = call.executeUpdate();
            return rowNum;
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return 0;
    }

    private int deleteCricketBattleLog(long userId, int limit) {
        String sql = "DELETE FROM t_u_cricket_battle_log WHERE id IN(" +
                "SELECT t1.id FROM (SELECT id FROM t_u_cricket_battle_log WHERE userId = ?) t1 WHERE t1.id" +
                " NOT IN (SELECT t2.id FROM (SELECT id FROM t_u_cricket_battle_log WHERE userId = ? ORDER BY createTime DESC LIMIT ?) t2))";
        ResultSet rs = null;
        CallableStatement call = null;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, userId);
            call.setInt(i++, limit);
            int rowNum = call.executeUpdate();
            return rowNum;
        } catch (SQLException ex) {
            getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return 0;
    }

    private long deleteTaskActivityData(long userId, List<Integer> taskActivityIdList, List<Integer> deleteActivityIdList) {
        List<Integer> activityIdList = selectAllActivityId(userId);
        long totalCount = 0;
        for (Integer activityId : activityIdList) {
            if(deleteActivityIdList.contains(activityId)){
                continue;
            }
            if(taskActivityIdList.contains(activityId)){
                deleteActivityIdList.add(activityId);
                int rowNum = deleteData(activityId);
                getLogger().error("expire activityId: {},count:{}", activityId, rowNum);
                totalCount += rowNum;
            }
        }
        return totalCount;
    }

    public List<Integer> selectAllActivityId(long userId) {
        String sql = "select DISTINCT activityId from t_u_activity_condition where userId=?";
        List<Integer> list = new ArrayList<>();
        ResultSet rs = null;
        CallableStatement call = null;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            call.setLong(1, userId);
            rs = call.executeQuery();
            while (rs.next()) {
                list.add(rs.getInt("activityId"));
            }
        } catch (SQLException ex) {
            getGameServer().getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return list;
    }

    public int deleteData(int activityId) {
        String sql = "delete from t_u_activity_condition where activityId = ?";
        ResultSet rs = null;
        CallableStatement call = null;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            call.setInt(1, activityId);
            int rowNum = call.executeUpdate();
            return rowNum;
        } catch (SQLException ex) {
            getGameServer().getLogger().error("异常错误", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
        return 0;
    }

    private void deleteVehicleOtherSkill(long userId) {
        String sql = "delete from t_u_vehicle_other_skill where userId = ? and vehicleUid in (select vehicleUid from t_u_vehicle where userId = ? and isDelete=1 and createTime < ?)";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() / 1000 - 100 * DateHelper.DAY_SECONDS;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, userId);
            call.setLong(i++, lastTime);
            int rowNum = call.executeUpdate();
            if(rowNum > 0) {
                getLogger().info("deleteVehicleOtherSkill userId:{},count:{}", userId, rowNum);
            }
        } catch (SQLException ex) {
            getLogger().error("deleteVehicleOtherSkill error:", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private void deleteVehicleSkill(long userId) {
        String sql = "delete from t_u_vehicle_skill where userId = ? and vehicleUid in (select vehicleUid from t_u_vehicle where userId = ? and isDelete=1 and createTime < ?)";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() / 1000 - 100 * DateHelper.DAY_SECONDS;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, userId);
            call.setLong(i++, lastTime);
            int rowNum = call.executeUpdate();
            if(rowNum > 0) {
                getLogger().info("deleteVehicleSkill userId:{},count:{}", userId, rowNum);
            }
        } catch (SQLException ex) {
            getLogger().error("deleteVehicleSkill error:", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }

    private void deleteVehicle(long userId) {
        String sql = "delete from t_u_vehicle where userId = ? and isDelete=1 and createTime < ?";
        ResultSet rs = null;
        CallableStatement call = null;
        long lastTime = System.currentTimeMillis() / 1000 - 100 * DateHelper.DAY_SECONDS;
        try {
            call = getGameServer().getConnection().prepareCall(sql);
            int i = 1;
            call.setLong(i++, userId);
            call.setLong(i++, lastTime);
            int rowNum = call.executeUpdate();
            if(rowNum > 0) {
                getLogger().info("deleteVehicle userId:{},count:{}", userId, rowNum);
            }
        } catch (SQLException ex) {
            getLogger().error("deleteVehicle,error:", ex);
        } finally {
            getGameServer().closeConnection(call, rs);
        }
    }
}
